var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery } = require('./server_util.js')

const log = require('../../../logger.js');
const db = require('../../../db/Redisdb.js');
const { sendHttp } = require('./ship_note.js');
var logger = require('../../../logger.js').logger;

function getClientIp(req) {
   // 检查是否设置了X-Forwarded-For头（通常用于代理和负载均衡器）
   const xForwardedFor = req.headers['x-forwarded-for'];
   console.log("xForwardedFor  " + xForwardedFor);
   if (xForwardedFor) {
      // X-Forwarded-For可能包含多个IP地址，通常第一个是最原始的客户端IP
      const forwardedIps = xForwardedFor.split(',');
      return forwardedIps[0].trim();
   }

   // 如果没有X-Forwarded-For头，则检查连接或套接字的remoteAddress属性
   const remoteAddress = req.connection.remoteAddress || req.socket.remoteAddress;
   console.log("remoteAddress  " + remoteAddress);
   // 处理IPv6映射的IPv4地址（例如::ffff:192.168.1.1）
   if (remoteAddress.startsWith('::ffff:')) {
      return remoteAddress.split('::ffff:')[1];
   }

   // 返回原始的remoteAddress
   return remoteAddress;
}

//单个物料淘汰供应商
router.post('/ttgys_wl', async function (req, res) {
   let { gysdata, ttyy, storage } = req.body
   console.log(gysdata, ttyy, "gysdata, ttyy");
   let iftt = await checkbysql(`select * from shipSRM_xm_ttmx where gysid='${gysdata.gysid}' and xmid='${gysdata.xmid}'
      and bjwlmxid='${gysdata.bjwlmxid}'`)
   if (iftt) {
      res_util(res, {}, true, "供应商该物料已淘汰")
      return
   }
   let id = await getxl_util()
   let sql = `insert into shipSRM_xm_ttmx (id,create_by,create_time,gysid,xmid,bjwlmxid,ttyy,ttsj,ttlc) values 
      ('${id}','${storage.ygxm}',getdate(),'${gysdata.gysid}','${gysdata.xmid}','${gysdata.bjwlmxid}','${ttyy}',getdate(),'${gysdata.bjlc ? gysdata.bjlc : 0}')`
   await usequery(sql)
   await saveczlog_util(sql, storage, '', '淘汰供应商', '新增', 'shipSRM_xm_ttmx')
   res_util(res, {}, true, "淘汰成功")
})

//淘汰供应商
router.post('/ttgys', async function (req, res) {
   let { gysdata, ttyy, storage } = req.body
   for (let i = 0; i < gysdata.length; i++) {
      let sql = `update shipSRM_xm_gysmx set sftt='是',ttyy='${ttyy}',ttsj=getdate() where mainid='${gysdata[i].mainid}'
      and gysid='${gysdata[i].gysid}'`
      await usequery(sql)
      let wlmxdata = await usequery(`select * from shipSRM_xm_wlmx where mainid='${gysdata[i].mainid}'`)
      for (let j = 0; j < wlmxdata.length; j++) {
         let wlmxitem = wlmxdata[j]
         let iftt = await checkbysql(`select * from shipSRM_xm_ttmx where gysid='${gysdata[i].gysid}' 
            and xmid='${wlmxitem.mainid}' and bjwlmxid='${wlmxitem.id}'`)
         if (iftt) {
            continue
         }
         let id = await getxl_util()
         let sql = `insert into shipSRM_xm_ttmx (id,create_by,create_time,gysid,xmid,bjwlmxid,ttyy,ttsj) values 
         ('${id}','${storage.ygxm}',getdate(),'${gysdata[i].gysid}','${wlmxitem.mainid}','${wlmxitem.id}','${ttyy}',getdate())`
         await usequery(sql)
      }
   }
   res_util(res, {}, true, "淘汰成功")
})

//查询项目是否已竞价结束
router.post('/query_ifjjzz', async function (req, res) {
   let { id } = req.body
   let sql = `select a.*,b.gysmc,b.sftt from shipSRM_xm a,shipSRM_xm_gysmx b where a.id=b.mainid and a.id='${id}'`
   let rtdata = await usequery(sql)
   res_util(res, { rtdata }, true, "查询成功")
})

//竞价终止
router.post('/jjzz', async function (req, res) {
   let { id, storage } = req.body
   let db = new Msdb()
   let sql = `update shipSRM_xm set jjzt='竞价结束',xmzt='竞价结束' where id='${id}'`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '竞价终止', '更新', 'shipSRM_xm')
   res_util(res, {}, true, "竞价终止成功")
})

//供应商应标
router.post('/gysyb', async function (req, res) {
   let { storage, formdata, fjmxdata } = req.body
   console.log(storage.userid, "=======================================storage.userid====================================当前gysid");
   let db = new Msdb()
   // await db.update(`update shipSRM_xm set bspbfj='${formdata.bspbfj.value?formdata.bspbfj.value:''}' where id='${formdata.id.value}'`)
   let sql = `update shipSRM_xm_gysmx set ybzt='已应标',ybsj=getdate() where mainid='${formdata.id.value}' and gysmc='${storage.ygxm}'`
   await db.update(sql)
   let delsql = `delete from shipSRM_gystb_fjmx where gysid='${storage.userid}' and xmid='${formdata.id.value}'`
   await db.update(delsql)
   for (let i = 0; i < fjmxdata.length; i++) {
      let fjitem = fjmxdata[i]
      let id = await getxl_util()
      let fjsql = `insert into shipSRM_gystb_fjmx (id,create_by,create_time,fjmxid,gysid,xmid,gys_fj,gys_fjscsj,gys_bz,gysmc) values 
      ('${id}','${storage.ygxm}',getdate(),'${fjitem.id}','${storage.userid}','${formdata.id.value}','${fjitem.gys_fj == null ? "" : fjitem.gys_fj}',getdate(),'${fjitem.gys_bz ? fjitem.gys_bz : ""}','${storage.ygxm}')`
      await db.update(fjsql)
      await saveczlog_util(fjsql, storage, '', '供应商应标上传附件明细', '新增', 'shipSRM_gystb_fjmx')
   }

   res_util(res, {}, true, "应标成功")
})

//供应商投标
router.post('/gystb', async function (req, res) {
   let { storage, formdata, fjmxdata, wlmxdata } = req.body
   console.log(storage.userid, "=======================================storage.userid====================================当前gysid");
   let db = new Msdb()
   let sql = `update shipSRM_xm_gysmx set ybzt='已投标',ybsj=getdate() where mainid='${formdata.id.value}' and gysmc='${storage.ygxm}'`
   await db.update(sql)
   let delsql = `delete from shipSRM_gystb_fjmx where gysid='${storage.userid}' and xmid='${formdata.id.value}'`
   await db.update(delsql)
   let ip = getClientIp(req)
   for (let i = 0; i < wlmxdata.length; i++) {
      let wlmxitem = wlmxdata[i]
      let bjid = await getxl_util()
      let mxsql = `insert into shipSRM_zb_tbjl (id,create_by,create_time,mainid,tbsj,tbgys,tbgysid,tbje,bjwlid,tbsm) values 
      ('${bjid}','${storage.ygxm}',getdate(),'${formdata.id.value}',getdate(),'${storage.ygxm}','${storage.userid}','${wlmxitem.tbje ? wlmxitem.tbje : 0}',
            '${wlmxitem.id}','${wlmxitem.bz}')`
      await db.update(mxsql)
   }
   for (let i = 0; i < fjmxdata.length; i++) {
      let fjitem = fjmxdata[i]
      let id = await getxl_util()
      let fjsql = `insert into shipSRM_gystb_fjmx (id,create_by,create_time,fjmxid,gysid,xmid,gys_fj,gys_fjscsj,gys_bz,gysmc) values 
      ('${id}','${storage.ygxm}',getdate(),'${fjitem.id}','${storage.userid}','${formdata.id.value}','${fjitem.gys_fj == null ? "" : fjitem.gys_fj}',getdate(),'${fjitem.gys_bz ? fjitem.gys_bz : ""}','${storage.ygxm}')`
      await db.update(fjsql)
      await saveczlog_util(fjsql, storage, '', '供应商应标上传附件明细', '新增', 'shipSRM_gystb_fjmx')
   }

   res_util(res, {}, true, "投标成功")
})

//供应商报价
router.post('/gysxj', async function (req, res) {
   let { storage, formdata, fjmxdata, wlmxdata } = req.body
   let rtdata = await usequery(`select * from shipSRM_xm where id=${formdata.id.value} and xjjzsj is not null`)
   if (rtdata.length > 0) {
      let xjjzdate = new Date(rtdata[0].xjjzsj.toISOString().slice(0, 19).replace('T', ' ')).getTime()
      if (xjjzdate < new Date().getTime()) {
         res_util(res, {}, false, "询价已结束，无法报价")
         return
      }
   }
   let db = new Msdb()
   let sql = `update shipSRM_xm_gysmx set ybzt='已报价',ybsj=getdate() where mainid='${formdata.id.value}' and gysmc='${storage.ygxm}'`
   await db.update(sql)
   for (let i = 0; i < fjmxdata.length; i++) {
      let iffj = await usequery(`select * from shipSRM_gystb_fjmx where fjmxid='${fjmxdata[i].id}' and gysid='${storage.userid}' 
         and xmid='${formdata.id.value}'`)
      //如果存在，则更新
      if (iffj.length > 0) {
         let updatefjsql = `update shipSRM_gystb_fjmx set gys_fj='${fjmxdata[i].gys_fj ? fjmxdata[i].gys_fj : ""}',gys_fjscsj=getdate(),
         gys_bz='${fjmxdata[i].gys_bz ? fjmxdata[i].gys_bz : ""}' where id='${iffj[0].id}'`
         await db.update(updatefjsql)
         await saveczlog_util(updatefjsql, storage, "供应商报价更新附件明细", "更新", "shipSRM_gystb_fjmx")
      }
      //不存在，则新增
      else {
         let fjitem = fjmxdata[i]
         let id = await getxl_util()
         let fjsql = `insert into shipSRM_gystb_fjmx (id,create_by,create_time,fjmxid,gysid,xmid,gys_fj,gys_fjscsj,gys_bz,gysmc) values 
         ('${id}','${storage.ygxm}',getdate(),'${fjitem.id}','${storage.userid}','${formdata.id.value}','${fjitem.gys_fj ? fjitem.gys_fj : ""}',
         getdate(),'${fjitem.gys_bz ? fjitem.gys_bz : ""}','${storage.ygxm}')`
         await db.update(fjsql)
         await saveczlog_util(fjsql, storage, '', '供应商报价上传附件明细', '新增', 'shipSRM_gystb_fjmx')
      }
   }
   let ip = getClientIp(req)
   for (let i = 0; i < wlmxdata.length; i++) {
      let wlmxitem = wlmxdata[i]
      if (wlmxitem.blbj == null) {
         continue
      }
      let ifbj = await usequery(`select * from shipSRM_gys_bjmx where bjwlmxid='${wlmxitem.id}' and gysid='${storage.userid}'
         and xmid='${wlmxitem.mainid}'`)
      //如果存在，则更新
      if (ifbj.length > 0) {
         let updatebjsql = `update shipSRM_gys_bjmx set blbj='${wlmxitem.blbj ? wlmxitem.blbj : ''}',bjsj=getdate(),sl='${wlmxitem.sl}',
         bjip='${ip}' where id='${ifbj[0].id}'`
         await db.update(updatebjsql)
         await saveczlog_util(updatebjsql, storage, "供应商报价更新报价明细", "更新", "shipSRM_gys_bjmx")
      }
      //不存在，则新增
      else {
         let bjid = await getxl_util()
         let mxsql = `insert into shipSRM_gys_bjmx (id,create_by,create_time,blbj,bjsj,gysid,gysmc,xmid,wlmc,ggxh,wlbm,bjwlmxid,bjip,sl) values 
         ('${bjid}','${storage.ygxm}',getdate(),'${wlmxitem.blbj ? wlmxitem.blbj : ''}',getdate(),'${storage.userid}','${storage.ygxm}','${wlmxitem.mainid}',
         '${wlmxitem.wlmc}','${wlmxitem.ggxh}','${wlmxitem.wlbm}','${wlmxitem.id}','${ip}','${wlmxitem.sl}')`
         await db.update(mxsql)
         await saveczlog_util(mxsql, storage, '', '供应商报价', '新增', 'shipSRM_gys_bjmx')
      }
   }

   res_util(res, {}, true, "报价成功")
})

//询价结束
router.post('/xjjs', async function (req, res) {
   let { storage, formdata } = req.body
   let sql = `update shipSRM_xm set xmzt='询价结束',xjjzsj=getdate() where id=${formdata.id.value}`
   await usequery(sql)
   await saveczlog_util(sql, storage, '', '询价结束', '更新', 'shipSRM_xm')
   res_util(res, {}, true, "询价结束成功")
})

//供应商报价
router.post('/save_gysjb_bj', async function (req, res) {
   let { id, bjjg, bjfj, dqlc, storage, wlmc, ggxh, wlbm, bjwlmxid, jjgz, sl, zbxs } = req.body
   let ifbg = await checkbysql(`select * from  shipSRM_xm where id='${id}' and jjzt!='竞价中'`)
   if (ifbg) {
      res_util(res, {}, true, "未到报价时间，不能报价")
      return
   }
   let ip = getClientIp(req)
   let db = new Msdb()
   let bjid = await getxl_util()
   let sql = `insert into shipSRM_gys_bjmx (id,create_by,create_time,blbj,bjsj,bjfj,gysid,gysmc,xmid,bjlc,wlmc,ggxh,wlbm,bjwlmxid,bjip,sl) values 
      ('${bjid}','${storage.ygxm}',getdate(),'${bjjg}',getdate(),'${bjfj}','${storage.userid}','${storage.ygxm}','${id}','${dqlc}',
      '${wlmc}','${ggxh}','${wlbm}','${bjwlmxid}','${ip}','${sl}')`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '供应商报价', '新增', 'shipSRM_gys_bjmx')
   //更新排名
   if (zbxs == '自由竞价') {
      let rtdata2 = await usequery(`			WITH LatestQuotes AS (
    SELECT 
    id,
        xmid,
        gysid,
        bjsj,
        blbj, -- 假设价格字段名为 jg（根据实际字段名调整）
        ROW_NUMBER() OVER (
            PARTITION BY xmid, gysid 
            ORDER BY bjsj DESC
        ) AS rn
    FROM shipsrm_gys_bjmx
    WHERE xmid = '${id}' -- 替换为实际项目ID
)
SELECT 
id,
    xmid,
    gysid,
    bjsj AS latest_bjsj,
    blbj AS quote_price,
    -- 高价排名（价格越高排名值越小）
    RANK() OVER (ORDER BY blbj DESC) AS high_price_rank,
    -- 低价排名（价格越低排名值越小）
    RANK() OVER (ORDER BY blbj ASC) AS low_price_rank
FROM LatestQuotes
WHERE rn = 1;`)

      for (let i = 0; i < rtdata2.length; i++) {
         await db.update(`update shipSRM_gys_bjmx set blpm='${jjgz == '价高优先' ? rtdata2[i].high_price_rank : rtdata2[i].low_price_rank}' where id='${rtdata2[i].id}'`)
      }

   }
   res_util(res, {}, true, "报价成功")
})

//供应商中标
router.post('/save_zbsz', async function (req, res) {
   let { zzmxdata, storage } = req.body
   
   // 如果没有中标明细数据，直接返回成功
   if (!zzmxdata || zzmxdata.length === 0) {
      res_util(res, {}, true, "中标信息设置成功")
      return
   }
   
   let db = new Msdb()
   //查下mainid是否已经存在 
   let ifcz = await checkbysql(`select * from  shipSRM_xm_zbmx where mainid='${zzmxdata[0].xmid}'`)
   if (ifcz) {
      res_util(res, {}, true, "项目已存在中标记录，操作失败")
      return
   }
   for (let i = 0; i < zzmxdata.length; i++) {
      let zzitem = zzmxdata[i]
      let zzid = await getxl_util()
      let sql = `insert into shipSRM_xm_zbmx (id,create_by,create_time,mainid,zbbl,zbje,bjwlmxid,gysmc,wlmc,wlbm,ggxh,dj,zbsl,dw) values 
      ('${zzid}','${storage.ygxm}',getdate(),'${zzitem.xmid}','${zzitem.zbbl ? zzitem.zbbl : 0}','${zzitem.zbje ? zzitem.zbje : 0}','${zzitem.bjwlmxid}',
      '${zzitem.gysmc}','${zzitem.wlmc}','${zzitem.wlbm}','${zzitem.ggxh}','${zzitem.zzbj}','${zzitem.zbsl ? zzitem.zbsl : 0}','${zzitem.dw ? zzitem.dw : ''}')`
      await db.update(sql)
      await saveczlog_util(sql, storage, '', '供应商中标', '新增', 'shipSRM_xm_zbmx')
   }
   res_util(res, {}, true, "中标信息设置成功")
})

//供应商中标确认
router.post('/gyszb_confirm', async function (req, res) {
   let { formdata, storage } = req.body
   let db = new Msdb()
   //查下mainid是否已经存在 
   await db.update(`update shipSRM_xm_gysmx set zbzt='已确认' where mainid='${formdata.id.value}' and gysmc='${storage.ygxm}'`)
   res_util(res, {}, true, "确认成功")
})

//完成遴选
router.post('/complete_lx', async function (req, res) {
   let { id, storage } = req.body
   let db = new Msdb()
   let sql = `update shipSRM_xm set xmzt='待竞价' where id='${id}'`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '完成遴选', '更新', 'shipSRM_xm')
   res_util(res, {}, true, "完成遴选成功")
})


//发布项目管理
router.post('/fb_xm', async function (req, res) {
   let { id, fbsj, ybjzsj, fblx } = req.body
   if (fblx == "立即发布") {
      var rtdata = await usequery(`update shipSRM_xm set xmzt='待应标',fbsj=getdate(),ybjzsj='${ybjzsj}' where id='${id}'`)
      // let item = await usequery(`select * from shipSRM_xm where id='${id}'`)
      // let gysList = await usequery(`select * from shipSRM_xm_gysmx where mainid='${id}'`)
      // let dhList = []
      // for (var j = 0; j < gysList.length; j++) {
      //    dhList.push(gysList[j].lxdh)
      //    console.log(gysList[j], "gysList[j].lxdh");
      // }
      // console.log(dhList, "dhList");
      // let msg = `您好，项目名称：${item[0].xmmc}已经开始应标了，应标截止时间：${item[0].ybjzsj.toISOString().slice(0, 19).replace('T', ' ')}，请您登录系统查看详情，系统地址为http://111.77.154.58:2233`
      // let msgresult = await sendHttp(dhList.join(','), msg)
      // for (var i = 0; i < dhList.length; i++) {
      //    let xlid = await getxl_util()
      //    await usequery(`insert into shipSRM_fsjl(id,create_time,create_by,phone,dxnr,fszt,tjsj,uid) 
      //       values('${xlid}',getdate(),'${item[0].create_by}','${dhList[i]}','${msg}','发送中',getdate(),'${JSON.parse(msgresult).uid}')`)
      // }
   } else {
      var rtdata = await usequery(`update shipSRM_xm set xmzt='待发布',fbsj='${fbsj}',ybjzsj='${ybjzsj}' where id='${id}'`)
   }
   res_util(res, { rtdata }, true, "发布成功")
})

//发布项目管理
router.post('/tjsh_xm', async function (req, res) {
   let { id } = req.body
   var rtdata = await usequery(`update shipSRM_xm set shzt='待审核' where id='${id}'`)
   res_util(res, { rtdata }, true, "发起审核成功")
})

//发布项目管理
router.post('/tgsh_xm', async function (req, res) {
   let { id } = req.body
   var rtdata = await usequery(`update shipSRM_xm set shzt='审核通过' where id='${id}'`)
   res_util(res, { rtdata }, true, "审核通过成功")
})

//发布项目管理
router.post('/bh_xm', async function (req, res) {
   let { id } = req.body
   var rtdata = await usequery(`update shipSRM_xm set shzt='驳回' where id='${id}'`)
   res_util(res, { rtdata }, true, "审核驳回成功")
})


//竞价发布
router.post('/fb_xm_jj', async function (req, res) {
   let { id, kbsj, fblx_jj } = req.body
   let db = new Msdb()
   let rtdata = await usequery(`select * from shipSRM_xm where id='${id}'`)
   if (fblx_jj == "立即发布") {
      let dqlkssj = new Date().toLocaleString()
      let dqljzsj = new Date(new Date(dqlkssj).getTime() + rtdata[0].mlcx * 60 * 1000).toLocaleString()
      await db.update(`update shipSRM_xm set xmzt='竞价中',dqlc=1,kbsj='${dqlkssj}',dqlkssj='${dqlkssj}',dqljzsj='${dqljzsj}',jjzt='竞价中' where id='${id}'`)
   } else {
      let dqljzsj = new Date(new Date(kbsj).getTime() + rtdata[0].mlcx * 60 * 1000).toLocaleString()
      console.log(dqljzsj);
      await db.update(`update shipSRM_xm set xmzt='待竞价',dqlc=1,kbsj='${kbsj}',dqlkssj='${kbsj}',dqljzsj='${dqljzsj}',jjzt='待竞价' where id='${id}'`)
   }
   res_util(res, {}, true, "操作成功")
})




//查询项目评标列表
router.post('/xmpblb', async function (req, res) {
   let { storage } = req.body
   var rtdata = await usequery(`SELECT a.*,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM shipSRM_xm_pbjl p 
            WHERE p.mainid = a.id 
        ) THEN '已评标'
        WHEN a.pbjssj IS NOT NULL AND getdate() > a.pbjssj THEN '已过期'
        ELSE '待评标'
    END AS pbzt
FROM shipSRM_xm a where a.zbxs='投标' and a.pbfzrid='${storage.userid}' order by create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})


//查询项目评标档案列表通过id
router.post('/query_xmpb_byid', async function (req, res) {

   let id = req.body.id
   let pblx = req.body.pblx
   let storage = req.body.storage
   let userid = storage.userid
   var data = await usequery(`select * from shipSRM_xm where id='${id}'`)
   if (data.length > 0) {
      //    if (pblx == "商务标书") {
      //       var pjmxdata = await usequery(`select l.*,COALESCE(g.swbdf, 0) AS swbdf,COALESCE(g.jsbdf, 0) AS jsbdf from 
      // (SELECT b.id+b.gysid as id,a.id as xmid,b.gys_fj,b.gysmc,b.gysid
      //        FROM shipSRM_xm a
      //        INNER JOIN
      //            (SELECT c.*,d.gys_fj,d.gys_fjscsj,d.gysmc,d.gysid
      //            FROM shipSRM_xm_fjmx c
      //            INNER JOIN shipSRM_gystb_fjmx d ON c.id = d.fjmxid
      //            WHERE c.wjlx = '商务标书') b
      //            ON a.id = b.mainid and a.id = '${data[0].id}'
      //   ) as l left join
      //  (select * from shipsrm_xm_kbmx where pbwyid = '${userid}' and  mainid = '${data[0].id}') g
      //  on l.gysid = g.gysid`)
      //    } else {
      //       var pjmxdata = await usequery(`select l.*,COALESCE(g.swbdf, 0) AS swbdf,COALESCE(g.jsbdf, 0) AS jsbdf from 
      //          (SELECT b.id+b.gysid as id,a.id as xmid,b.gys_fj,b.gysmc,b.gysid
      //                 FROM shipSRM_xm a
      //                 INNER JOIN
      //                     (SELECT c.*,d.gys_fj,d.gys_fjscsj,d.gysmc,d.gysid
      //                     FROM shipSRM_xm_fjmx c
      //                     INNER JOIN shipSRM_gystb_fjmx d ON c.id = d.fjmxid
      //                     WHERE c.wjlx = '技术标书') b
      //                     ON a.id = b.mainid and a.id = '${data[0].id}'
      //            ) as l left join
      //           (select * from shipsrm_xm_kbmx where pbwyid = '${userid}' and  mainid = '${data[0].id}') g
      //           on l.gysid = g.gysid`)
      //    }
      //    var gysbjmxdata = await usequery(`select a.*,DENSE_RANK() OVER (PARTITION BY bjwlmxid ORDER BY bj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'} ) AS rank from (
      //   select xmid,gysid,bjwlmxid,${data[0].jjgz == '价高优先' ? 'MAX(blbj)' : 'MIN(blbj)'} as bj 
      //   from  shipsrm_gys_bjmx where xmid='${data[0].id}' and sftb is null
      //   GROUP BY xmid,gysid,bjwlmxid
      // ) as a`)
      var tbbjdata = await usequery(`select * from shipSRM_zb_tbjl where mainid='${id}'`)
      var wlmxdata = await usequery(`select * from shipSRM_xm_wlmx where mainid='${id}'`)
      var gysmxdata = await usequery(`select * from shipSRM_xm_gysmx where mainid='${id}'`)
      var pbmxdata = await usequery(`select * from shipSRM_xm_pbmd where mainid='${id}'`)
      var kbmxdata = await usequery(`select * from shipSRM_xm_kbmx where mainid='${id}'`)
      var ttmxdata = await usequery(`select * from shipSRM_xm_ttmx where xmid='${id}'`)
      var tbfjdata = await usequery(`select * from shipSRM_gystb_fjmx where xmid='${id}'`)
      res_util(res, { formdata: data[0], wlmxdata, gysmxdata, pbmxdata, kbmxdata, ttmxdata, tbbjdata, tbfjdata }, true, "查询成功")
   } else {
      res_util(res, {}, false, "查询失败")
   }
})

// router.post('/xmpb_tjpf', async function (req, res) {

//    let { pjmxdata, id, storage, formdata, pblx } = req.body
//    var db = new Msdb()
//    console.log(pjmxdata, id, "评分数据");
//    for (var pjmxkey in pjmxdata) {
//       let pjmxitem = pjmxdata[pjmxkey]
//       let mxid = await getxl_util()
//       console.log(pjmxitem, "评分数据");
//       await db.update(`insert into shipsrm_xm_kbmx (id,mainid,pbwy,pbwyid,xmid,gys,gysid,swbdf,jsbdf,swbqz,jsbqz,bslx) values ('${mxid}','${id}','${storage.ygxm}','${storage.userid}','${id}','${pjmxitem.gysmc}','${pjmxitem.gysid}','${pjmxitem.swbdf}','${pjmxitem.jsbdf}','${formdata.swbqz.value}','${formdata.jsbqz.value}','${pblx}')`)
//    }
//    let me_sql = `WITH ScoreSummary AS (
//     SELECT 
//         k.mainid,
//         k.gysid,
//         COALESCE(AVG(CASE WHEN k.bslx = '技术标书' THEN k.jsbdf END), 0) AS jsdf,  -- 技术得分
//         COALESCE(AVG(CASE WHEN k.bslx = '商务标书' THEN k.swbdf END), 0) AS swdf   -- 商务得分
//     FROM 
//         shipSRm_xm_kbmx k
//     GROUP BY 
//         k.mainid, k.gysid
// ),
// -- 计算最后得分和排名
// FinalScore AS (
//     SELECT 
//         s.mainid,
//         s.gysid,
//         s.jsdf,
//         s.swdf,
//         round((s.jsdf * x.jsbqz + s.swdf * x.swbqz), 2) AS zhdf  -- 最后得分 = 技术得分 * 技术权重 + 商务得分 * 商务权重
//     FROM 
//         ScoreSummary s
//     INNER JOIN 
//         shipsrm_xm x ON s.mainid = x.id
// )
// UPDATE g
// SET 
//     g.jsdf = f.jsdf,
//     g.swdf = f.swdf,
//     g.zhdf = f.zhdf,
//     g.pm = ranked.pm
// FROM 
//     shipsrm_xm_gysmx g
// INNER JOIN 
//     FinalScore f ON g.mainid = f.mainid AND g.gysid = f.gysid
// INNER JOIN (
//     SELECT 
//         mainid,
//         gysid,
//         zhdf,
//         RANK() OVER (PARTITION BY mainid ORDER BY zhdf DESC) AS pm
//     FROM 
//         FinalScore
// ) ranked ON f.mainid = ranked.mainid AND f.gysid = ranked.gysid;`
//    await db.update(me_sql)

//    await saveczlog_util(me_sql, storage, '', '评委提交评分并修改gysmx 得分和排名', '新增', 'shipSRM_xm')

//    let jlid = await getxl_util()
//    await db.update(`insert into shipSRM_xm_pbjl (id,mainid,pbwy,pbwyid,xmid,pbrq) values ('${jlid}','${id}','${storage.ygxm}','${storage.userid}','${id}',getdate())`)
//    res_util(res, {}, true, "评分成功")
// })

router.post('/xmpb_tjpf', async function (req, res) {

   let { pjswmxdata, pjjsmxdata, id, storage, formdata, pblx, gysmxdata } = req.body
   var db = new Msdb()
   await db.update(`update shipSRM_xm set bspbfj='${formdata.bspbfj.value ? formdata.bspbfj.value : ''}' where id='${formdata.id.value}'`)
   // console.log(pjswmxdata, pjjsmxdata, id, "评分数据");
   for (let i = 0; i < pjswmxdata.length; i++) {
      let pjswmxitem = pjswmxdata[i]
      let pjjsmxitem = pjjsmxdata[i]
      // let mxid = await getxl_util()
      for (var key in pjswmxitem) {
         if (key != 'xuhao' && key != 'id' && key != 'mainid' && key != 'wlmxid' && key != 'gysmc' && key != 'gysid' && key != 'key' && key != 'zzdf'
            && key != 'bdj' && key != 'tbj' && key != 'xtpf'
         ) {
            if (pjswmxitem[key]) {
               let rtdata = await usequery(`select * from shipSRM_xm_kbmx where mainid='${id}' and pbwy='${key}' and wlmxid='${pjswmxitem.wlmxid}' and gysid='${pjswmxitem.gysid}'`)
               if (rtdata.length > 0) {
                  let sql = `update shipSRM_xm_kbmx set swbdf='${pjswmxitem[key]}' where id='${rtdata[0].id}'`
                  await db.update(sql)
               }
               else {
                  let mxid = await getxl_util()
                  let sql = `insert into shipSRM_xm_kbmx (id,mainid,pbwy,xmid,gys,gysid,swbdf,wlmxid,bslx) values 
                 ('${mxid}','${id}','${key}','${id}','${pjswmxitem.gysmc}','${pjswmxitem.gysid}','${pjswmxitem[key]}','${pjswmxitem.wlmxid}','商务标书')`
                  await db.update(sql)
               }
            }
         }
      }
      for (var key in pjjsmxitem) {
         if (key != 'xuhao' && key != 'id' && key != 'mainid' && key != 'wlmxid' && key != 'gysmc' && key != 'gysid' && key != 'key' && key != 'zzdf') {
            if (pjjsmxitem[key]) {
               let rtdata = await usequery(`select * from shipSRM_xm_kbmx where mainid='${id}' and pbwy='${key}' and wlmxid='${pjjsmxitem.wlmxid}' and gysid='${pjjsmxitem.gysid}'`)
               if (rtdata.length > 0) {
                  let sql = `update shipSRM_xm_kbmx set jsbdf='${pjjsmxitem[key]}' where id='${rtdata[0].id}'`
                  await db.update(sql)
               }
               else {
                  let mxid = await getxl_util()
                  let sql = `insert into shipSRM_xm_kbmx (id,mainid,pbwy,xmid,gys,gysid,jsbdf,wlmxid,bslx) values 
               ('${mxid}','${id}','${key}','${id}','${pjjsmxitem.gysmc}','${pjjsmxitem.gysid}','${pjjsmxitem[key]}','${pjjsmxitem.wlmxid}','技术标书')`
                  await db.update(sql)
               }
            }
         }
      }
   }
   for (let i = 0; i < gysmxdata.length; i++) {
      let gysmxitem = gysmxdata[i]
      var rtdata = await usequery(`select * from shipsrm_xm_gyszfmx where mainid='${gysmxitem.mainid}' and gysid='${gysmxitem.id}' and wlmxid='${gysmxitem.wlmxid}'`)
      if (rtdata.length > 0) {
         let sql = `update shipsrm_xm_gyszfmx set jsdf='${gysmxitem.jsdf}',swdf='${gysmxitem.swdf}',zhdf='${gysmxitem.zhdf}',
         pm='${gysmxitem.pm}' where id='${rtdata[0].id}'`
         await db.update(sql)
      }
      else {
         let mxid = await getxl_util()
         let sql = `insert into shipsrm_xm_gyszfmx (id,mainid,gysid,wlmxid,jsbdf,swbdf,zzdf) values 
         ('${mxid}','${gysmxitem.mainid}','${gysmxitem.gysid}','${gysmxitem.wlmxid}','${gysmxitem.jsdf}','${gysmxitem.swdf}',
         '${gysmxitem.zhdf}')`
         await db.update(sql)
      }
   }

   let jlid = await getxl_util()
   let me_sql = `insert into shipSRM_xm_pbjl (id,mainid,pbwy,pbwyid,xmid,pbrq) values ('${jlid}','${id}','${storage.ygxm}','${storage.userid}','${id}',getdate())`
   await db.update(me_sql)
   await saveczlog_util(me_sql, storage, '', '评委提交评分并修改gysmx 得分和排名', '新增', 'shipSRM_xm')
   res_util(res, {}, true, "评分成功")
})

//查询项目管理档案列表
router.post('/xmlb', async function (req, res) {
   let { xmmc, zbpl } = req.body
   var rtdata = await usequery(`select a.*,      
      STUFF((
        SELECT ',' + b.gysmc
        FROM shipSRM_xm_gysmx b
        WHERE b.mainid = a.id and b.ybzt='已应标'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ybgysmc from shipSRM_xm a
      where a.xmmc like '%${xmmc}%' and a.zbpl like '%${zbpl}%' order by a.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询竞价项目列表
router.post('/xmjj_list', async function (req, res) {
   let { xmmc, zbpl } = req.body
   var rtdata = await usequery(`select a.*,      
      STUFF((
        SELECT ',' + b.gysmc
        FROM shipSRM_xm_gysmx b
        WHERE b.mainid = a.id and b.ybzt='已应标'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ybgysmc from shipSRM_xm a
      where a.xmmc like '%${xmmc}%' and a.zbpl like '%${zbpl}%' and a.zbxs='竞价' order by a.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询自由竞价项目列表
router.post('/zyjj_list', async function (req, res) {
   let { xmmc, zbpl } = req.body
   var rtdata = await usequery(`select a.*,      
      STUFF((
        SELECT ',' + b.gysmc
        FROM shipSRM_xm_gysmx b
        WHERE b.mainid = a.id and b.ybzt='已应标'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ybgysmc from shipSRM_xm a
      where a.xmmc like '%${xmmc}%' and a.zbpl like '%${zbpl}%' and a.zbxs='自由竞价' order by a.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询投标项目列表
router.post('/xmtb_list', async function (req, res) {
   let { xmmc, zbpl } = req.body
   var rtdata = await usequery(`select a.*,      
      STUFF((
        SELECT ',' + b.gysmc
        FROM shipSRM_xm_gysmx b
        WHERE b.mainid = a.id and b.ybzt='已投标'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ybgysmc from shipSRM_xm a
      where a.xmmc like '%${xmmc}%' and a.zbpl like '%${zbpl}%' and a.zbxs='投标' order by a.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询遴选项目列表
router.post('/xmls_list', async function (req, res) {
   let { xmmc, zbpl } = req.body
   var rtdata = await usequery(`select * from shipSRM_xm where zbxs='投标' order by create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询询价项目列表
router.post('/xmxj_list', async function (req, res) {
   let { xmmc, zbpl } = req.body
   var rtdata = await usequery(`select a.* from shipSRM_xm a
      where a.xmmc like '%${xmmc}%' and a.zbpl like '%${zbpl}%' and a.zbxs='询价' order by a.create_time desc`)
   for (var i = 0; i < rtdata.length; i++) {
      var wlmxdata = await usequery(`select * from shipSRM_xm_wlmx where mainid='${rtdata[i].id}'`)
      var bjmxdata = await usequery(`select * from shipSRM_gys_bjmx where xmid='${rtdata[i].id}'`)
      var fjmxdata = await usequery(`select a.*,b.id as bjwlmxid from shipSRM_gystb_fjmx a left join shipSRM_xm_wlmx b 
         on a.xmid=b.mainid where a.xmid='${rtdata[i].id}'`)
      rtdata[i].wlmxdata = wlmxdata
      for (var j = 0; j < bjmxdata.length; j++) {
         bjmxdata[j].gys_fj = fjmxdata.filter(item => item.bjwlmxid == bjmxdata[j].bjwlmxid && item.gysid == bjmxdata[j].gysid)[0]?.gys_fj
      }
      for (var j = 0; j < fjmxdata.length; j++) {
         if (!bjmxdata.some(item => item.bjwlmxid == fjmxdata[j].bjwlmxid && item.gysid == fjmxdata[j].gysid)) {
            console.log(fjmxdata[j], "aaaaaaaa");
            bjmxdata.push({
               bjwlmxid: fjmxdata[j].bjwlmxid,
               gys_fj: fjmxdata[j].gys_fj,
               gysmc: fjmxdata[j].gysmc,
               bjsj: fjmxdata[j].gys_fjscsj
            })
         }
      }
      rtdata[i].bjmxdata = bjmxdata
      console.log(rtdata[i].bjmxdata, "element");
   }
   res_util(res, { rtdata }, true, "查询成功")
})

//查询项目管理供应商应标列表
router.post('/xmlb_gysyb', async function (req, res) {
   let { xmmc, zbpl, storage } = req.body
   var rtdata = await usequery(`select a.ybzt,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id 
      and b.xmmc like '%${xmmc}%' and b.zbpl like '%${zbpl}%' and (b.xmzt= '待应标' or a.ybzt = '已应标') and b.zbxs!='询价' and b.shzt ='审核通过'
      and a.gysmc='${storage.ygxm}' order by b.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询项目管理供应商投标列表
router.post('/xmlb_gystb', async function (req, res) {
   let { xmmc, zbpl, storage } = req.body
   var rtdata = await usequery(`select a.ybzt,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id 
      and b.xmmc like '%${xmmc}%' and b.zbpl like '%${zbpl}%'  and b.zbxs='投标' and b.shzt ='审核通过'
      and a.gysmc='${storage.ygxm}' order by b.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询项目管理供应商询价列表
router.post('/xmlb_gysxj', async function (req, res) {
   let { xmmc, zbpl, storage } = req.body
   var rtdata = await usequery(`select a.ybzt,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id 
      and b.xmmc like '%${xmmc}%' and b.zbpl like '%${zbpl}%' and (b.xmzt= '询价中' or b.xmzt= '询价结束') and b.shzt ='审核通过'
      and b.zbxs='询价' and a.gysmc='${storage.ygxm}' order by b.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询项目管理供应商竞标列表
router.post('/xmlb_gysjb', async function (req, res) {
   let { xmmc, zbpl, storage } = req.body
   var rtdata = await usequery(`select a.ybzt,a.sftt,a.ttyy,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id and (a.ybzt='已应标'
      or a.ybzt='已投标') and b.xmmc like '%${xmmc}%' and b.zbpl like '%${zbpl}%' and (b.xmzt='待竞价' or b.xmzt='竞价中' or b.xmzt='竞价结束') 
      and a.gysmc='${storage.ygxm}' order by b.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询项目管理供应商竞标列表
router.post('/xmlb_gysbqxj', async function (req, res) {
   let { xmmc, zbpl, storage } = req.body
   var rtdata = await usequery(`select a.ybzt,a.sftt,a.ttyy,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id 
      and b.xmmc like '%${xmmc}%' and b.zbpl like '%${zbpl}%' and (b.zbxs='投标' and b.xmzt!='待发布') and a.gysmc='${storage.ygxm}'`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询项目管理供应商中标列表
router.post('/xmlb_gyszb', async function (req, res) {
   let { xmmc, zbpl, storage } = req.body
   var rtdata = await usequery(`select b.*,c.zbzt from (select DISTINCT gysmc,mainid from shipSRM_xm_zbmx) a,shipSRM_xm b,shipSRM_xm_gysmx c where a.mainid=b.id 
      and b.xmmc like '%${xmmc}%' and b.zbpl like '%${zbpl}%' and  b.xmzt='竞价结束' and a.gysmc='${storage.ygxm}' and c.mainid=b.id 
      and c.gysmc='${storage.ygxm}' order by b.create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询项目管理供应商中标列表
router.post('/xmlb_zbfp', async function (req, res) {
   let { xmmc, zbpl, storage } = req.body
   var rtdata = await usequery(`
      SELECT 
         main.*,
         ISNULL(zbmxCount.SubTableCount, 0) AS SubTableCount,
         ISNULL(unconfirmedCount.UnconfirmedCount, 0) AS UnconfirmedCount,
         ISNULL(validBidCount.ValidBidCount, 0) AS ValidBidCount,
         CASE 
            WHEN ISNULL(zbmxCount.SubTableCount, 0) = 0 AND ISNULL(validBidCount.ValidBidCount, 0) = 0 THEN '已确认'
            WHEN ISNULL(zbmxCount.SubTableCount, 0) = 0 AND ISNULL(validBidCount.ValidBidCount, 0) > 0 THEN '需分配'
            WHEN ISNULL(unconfirmedCount.UnconfirmedCount, 0) > 0 THEN '确认中'
            ELSE '已确认'
         END AS zbfpzt
      FROM shipSRM_xm as main 
      OUTER APPLY (
         SELECT COUNT(*) AS SubTableCount 
         FROM shipSRM_xm_zbmx as sub 
         WHERE sub.mainid = main.id
      ) AS zbmxCount
      OUTER APPLY (
         SELECT COUNT(*) AS UnconfirmedCount 
         FROM shipSRM_xm_gysmx as gysmx
         INNER JOIN shipSRM_xm_zbmx as zbmx ON gysmx.mainid = zbmx.mainid AND gysmx.gysmc = zbmx.gysmc
         WHERE gysmx.mainid = main.id AND gysmx.zbzt IS NULL
      ) AS unconfirmedCount
      OUTER APPLY (
         SELECT COUNT(*) AS ValidBidCount 
         FROM shipSRM_gys_bjmx as bj
         INNER JOIN shipSRM_xm_gysmx as gysmx ON bj.xmid = gysmx.mainid AND bj.gysid = gysmx.gysid
         WHERE bj.xmid = main.id 
           AND COALESCE(gysmx.sftt,'否') != '是'
           AND NOT EXISTS (
              SELECT 1 FROM shipSRM_xm_ttmx tt 
              WHERE tt.xmid = bj.xmid 
                AND tt.gysid = bj.gysid 
                AND tt.bjwlmxid = bj.bjwlmxid
           )
      ) AS validBidCount
      WHERE main.xmmc like '%${xmmc}%' 
        AND main.zbpl like '%${zbpl}%' 
        AND main.xmzt='竞价结束' 
      ORDER BY main.create_time desc
   `)
   res_util(res, { rtdata }, true, "查询成功")
})

//查询项目管理供应商竞价中列表
router.post('/xmlb_jjzc', async function (req, res) {
   let { xmmc, zbpl, storage } = req.body
   var rtdata = await usequery(`select * from shipSRM_xm where  jjzt='竞价中' or jjzt='竞价结束' or jjzt='竞价休息' order by create_time desc`)
   res_util(res, { rtdata }, true, "查询成功")
})


//查询项目管理档案列表通过id
router.post('/query_xm_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   if (data.length > 0) {
      var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var zbmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_zbmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysmxdata = await usequery(`select b.id,b.gysmc,b.gysid,b.shtyxydm,b.lxr,b.lxdh,b.ybzt,b.mainid,b.ybsj,round(b.zhdf,2) as zhdf,b.pm,b.jsdf,b.swdf from shipSRM_xm a,shipSRM_xm_gysmx b where a.id=b.mainid and a.id='${data[0].id}' order by b.pm asc`)
      var fjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_fjmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysfjmxdata = await usequery(`select b.* from shipSRM_xm a,
      (select c.*,d.gys_fj,d.gys_fjscsj,d.gysmc from shipSRM_xm_fjmx c,shipSRM_gystb_fjmx d where c.id=d.fjmxid) b
      where a.id=b.mainid and b.gys_fj!='' and a.id='${data[0].id}'`)
      var pbmddata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_pbmd b where a.id=b.mainid and a.id='${data[0].id}'`)
      var bjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_gys_bjmx b where a.id=b.xmid and a.id='${data[0].id}'`)
      res_util(res, { formdata: data[0], wlmxdata, zbmxdata, gysmxdata, fjmxdata, gysfjmxdata, bjmxdata, pbmddata }, true, "查询成功")
   } else {
      res_util(res, {}, false, "查询失败")
   }
})

//查询项目管理档案列表通过id
router.post('/query_xm_byid_xj', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   if (data.length > 0) {
      var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var zbmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_zbmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysmxdata = await usequery(`select b.id,b.gysmc,b.gysid,b.shtyxydm,b.lxr,b.lxdh,b.ybzt,b.mainid,b.ybsj,round(b.zhdf,2) as zhdf,b.pm,b.jsdf,b.swdf from shipSRM_xm a,shipSRM_xm_gysmx b where a.id=b.mainid and a.id='${data[0].id}' order by b.pm asc`)
      var fjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_fjmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysfjmxdata = await usequery(`select b.* from shipSRM_xm a,
      (select c.*,d.gys_fj,d.gys_fjscsj,d.gysmc from shipSRM_xm_fjmx c,shipSRM_gystb_fjmx d where c.id=d.fjmxid) b
      where a.id=b.mainid and a.id='${data[0].id}'`)
      var pbmddata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_pbmd b where a.id=b.mainid and a.id='${data[0].id}'`)
      var bjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_gys_bjmx b where a.id=b.xmid and a.id='${data[0].id}'`)
      res_util(res, { formdata: data[0], wlmxdata, zbmxdata, gysmxdata, fjmxdata, gysfjmxdata, bjmxdata, pbmddata }, true, "查询成功")
   } else {
      res_util(res, {}, false, "查询失败")
   }
})

//查询项目管理档案列表通过id
router.post('/query_xm_byid_tb', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   if (data.length > 0) {
      var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var zbmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_zbmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysmxdata = await usequery(`select b.id,b.gysmc,b.gysid,b.shtyxydm,b.lxr,b.lxdh,b.ybzt,b.mainid,b.ybsj,b.sftt,b.ttyy from 
         shipSRM_xm_gysmx b where  mainid='${data[0].id}' `)
      var fjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_fjmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysfjmxdata = await usequery(`select b.* from 
      (select c.wjlx,c.wjmc,c.wjyq,c.mainid,d.id,d.gys_fj,d.gys_fjscsj,d.gysmc from shipSRM_xm_fjmx c,shipSRM_gystb_fjmx d where c.id=d.fjmxid and d.gys_fj !='') b
      where b.mainid = '${data[0].id}'`)
      var pbmddata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_pbmd b where a.id=b.mainid and a.id='${data[0].id}'`)
      var bjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_gys_bjmx b where a.id=b.xmid and a.id='${data[0].id}'`)
      res_util(res, { formdata: data[0], wlmxdata, zbmxdata, gysmxdata, fjmxdata, gysfjmxdata, bjmxdata, pbmddata }, true, "查询成功")
   } else {
      res_util(res, {}, false, "查询失败")
   }
})

//查询项目管理档案列表通过id (评标后遴选)
router.post('/query_xm_byid_ls', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   if (data.length > 0) {
      var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var zbmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_zbmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysmxdata = await usequery(`select l.*,l.gysid as id,r.zzdf,r.rank from 
	 (select a.gysmc,a.gysid,a.lxr,a.lxdh,b.id as wlmxid from shipSRM_xm_gysmx a INNER JOIN 
   shipSRM_xm_wlmx b on a.mainid=b.mainid and a.mainid='${data[0].id}') as l
  left join
   (select a.*,DENSE_RANK() OVER (PARTITION BY wlmxid ORDER BY zzdf desc) AS rank from (
     select xmid,gysid,wlmxid,ROUND(AVG(zzdf), 2) as zzdf
     from  shipSRM_xm_kbmx where xmid='${data[0].id}'
     GROUP BY xmid,gysid,wlmxid
   ) as a) as r 
	 on l.gysid=r.gysid and l.wlmxid =r.wlmxid order by r.rank asc`)
      var fjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_fjmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysfjmxdata = await usequery(`select b.* from shipSRM_xm a,
      (select c.*,d.gys_fj,d.gys_fjscsj,d.gysmc from shipSRM_xm_fjmx c,shipSRM_gystb_fjmx d where c.id=d.fjmxid) b
      where a.id=b.mainid and a.id='${data[0].id}'`)
      var pbmddata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_pbmd b where a.id=b.mainid and a.id='${data[0].id}'`)
      var bjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_gys_bjmx b where a.id=b.xmid and a.id='${data[0].id}'`)
      res_util(res, { formdata: data[0], wlmxdata, zbmxdata, gysmxdata, fjmxdata, gysfjmxdata, bjmxdata, pbmddata }, true, "查询成功")
   } else {
      res_util(res, {}, false, "查询失败")
   }
})

//查询项目管理档案列表通过id (标前询价后遴选)
router.post('/query_xm_byid_ls2', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   if (data.length > 0) {
      var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var zbmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_zbmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      //       var gysmxdata = await usequery(`select l.*,l.gysid as id,r.bj,r.rank,c.ttyy from 
      // 	 (select a.gysmc,a.gysid,a.lxr,a.lxdh,a.mainid,a.mainid as xmid,b.id as wlmxid from shipSRM_xm_gysmx a INNER JOIN 
      //    shipSRM_xm_wlmx b on a.mainid=b.mainid and a.mainid='${data[0].id}') as l
      //    left join shipSRM_xm_ttmx c on l.gysid=c.gysid and l.wlmxid=c.bjwlmxid
      //   left join
      //    (select a.*,DENSE_RANK() OVER (PARTITION BY bjwlmxid ORDER BY bj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'} ) AS rank from (
      //      select xmid,gysid,bjwlmxid,${data[0].jjgz == '价高优先' ? 'MAX(blbj)' : 'MIN(blbj)'} as bj 
      //      from  shipsrm_gys_bjmx where xmid='${data[0].id}'
      //      GROUP BY xmid,gysid,bjwlmxid
      //    ) as a) as r 
      // 	 on l.gysid=r.gysid and l.wlmxid =r.bjwlmxid order by r.rank asc`)
      var gysmxdata = await usequery(`select l.*,l.gysid as id,c.ttyy,d.zzdf,d.jsbdf,d.swbdf from 
	 (select a.gysmc,a.gysid,a.lxr,a.lxdh,a.mainid,a.mainid as xmid,b.id as wlmxid from shipSRM_xm_gysmx a INNER JOIN 
   shipSRM_xm_wlmx b on a.mainid=b.mainid and a.mainid='${data[0].id}') as l
   left join shipSRM_xm_ttmx c on l.gysid=c.gysid and l.wlmxid=c.bjwlmxid
   left join shipsrm_xm_gyszfmx d on l.gysid=d.gysid and l.wlmxid=d.wlmxid and d.mainid='${data[0].id}'
   `)
      var fjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_fjmx b where a.id=b.mainid and a.id='${data[0].id}'`)
      var gysfjmxdata = await usequery(`select b.* from shipSRM_xm a,
      (select c.*,d.gys_fj,d.gys_fjscsj,d.gysmc from shipSRM_xm_fjmx c,shipSRM_gystb_fjmx d where c.id=d.fjmxid) b
      where a.id=b.mainid and a.id='${data[0].id}'`)
      var pbmddata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_pbmd b where a.id=b.mainid and a.id='${data[0].id}'`)
      var bjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_gys_bjmx b where a.id=b.xmid and a.id='${data[0].id}'`)
      res_util(res, { formdata: data[0], wlmxdata, zbmxdata, gysmxdata, fjmxdata, gysfjmxdata, bjmxdata, pbmddata }, true, "查询成功")
   } else {
      res_util(res, {}, false, "查询失败")
   }
})

//查询供应商应标档案列表通过id
router.post('/query_gysyb_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data[0].id}'`)
   var fjmxdata = await usequery(`select b.* from shipSRM_xm a,
      (select c.*,d.gys_fj from shipSRM_xm_fjmx c left join shipSRM_gystb_fjmx d on c.id=d.fjmxid and d.gysid='${req.body.storage.userid}' ) b
      where a.id=b.mainid and a.id='${data[0].id}'`)
   res_util(res, { formdata: data[0], wlmxdata, fjmxdata }, true, "查询成功")
})




//查询供应商应标档案列表通过id
router.post('/query_gysxj_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   var wlmxdata = await usequery(`select a.*,b.blbj from shipSRM_xm_wlmx a left join shipSRM_gys_bjmx b on a.id=b.bjwlmxid  and b.gysid='${req.body.storage.userid}' 
      where a.mainid='${data[0].id}'`)
   var fjmxdata = await usequery(`select b.* from shipSRM_xm a,
      (select c.*,d.gys_fj from shipSRM_xm_fjmx c left join shipSRM_gystb_fjmx d on c.id=d.fjmxid and d.gysid='${req.body.storage.userid}' ) b
      where a.id=b.mainid and a.id='${data[0].id}'`)
   res_util(res, { formdata: data[0], wlmxdata, fjmxdata }, true, "查询成功")
})

//查询供应商竞价报价档案列表通过id
router.post('/query_gysjb_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   if (data[0].jjzt == '竞价中') {
      //获取倒计时 秒数
      let djs = (new Date(data[0].dqljzsj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
      data[0].djs = djs.toFixed(0)
   }
   else if (data[0].jjzt == '待竞价') {
      //获取倒计时 秒数
      if (data[0].dqlkssj) {
         let djs = (new Date(data[0].dqlkssj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
         // console.log(djs, "一开始的倒计时");
         data[0].djs = djs.toFixed(0)
      }
      else {
         data[0].djs = 999999
      }
   }
   else if (data[0].jjzt == '竞价休息') {
      if (new Date(data[0].dqljzsj).getTime() + (data[0].mljg * 60 * 1000) < new Date().getTime()) {
         let djs = (new Date().getTime() + 8 * 60 * 60 * 1000 - new Date(data[0].dqljzsj).getTime() - (data[0].mljg * 60 * 1000)) / 1000
         data[0].djs = djs.toFixed(0)
      }
      else {
         let djs = (new Date(data[0].dqljzsj).getTime() + (data[0].mljg * 60 * 1000) - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
         data[0].djs = djs.toFixed(0)
      }
   }
   else if (data[0].jjzt == '竞价结束') {
      data[0].djs = 0
   }
   else {
      data[0].djs = 0
   }
   var wlmxdata = await usequery(`
      select d.*,SubQuery.SubTableCount from 
         (
            select b.*,c.dqbj,c.pm from shipSRM_xm_wlmx b left join
            (
               select c.bjwlmxid,c.blbj as dqbj,c.pm,c.gysid from (
                 select bjwlmxid,blbj,gysid,xmid,
                 DENSE_RANK() over(partition by bjwlmxid order by blbj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'} ) as pm from   
                    (
                     select  ROW_NUMBER() OVER (PARTITION BY bjwlmxid,gysid ORDER BY blbj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'} ) AS rank, *
                     from shipSRM_gys_bjmx where xmid='${req.body.id}'
                     ) as a  where rank=1
                  ) c
                  where c.gysid='${req.body.storage.userid}'
            ) as c on b.id=c.bjwlmxid
         ) as d
				 OUTER APPLY (SELECT COUNT(*) AS SubTableCount FROM shipSRM_xm_ttmx as tt WHERE  tt.xmid = d.mainid and 
						tt.gysid='${req.body.storage.userid}' and tt.bjwlmxid=d.id) AS SubQuery  
         where d.mainid='${data[0].id}' and SubTableCount = 0`)
   if (data[0].zbxs == "竞价") {
      var bjmxdata = await usequery(`select a.*,b.ttlc,b.ttyy from shipSRM_gys_bjmx a left join shipSRM_xm_ttmx b on a.xmid=b.xmid 
            and a.bjwlmxid=b.bjwlmxid and a.gysid=b.gysid and a.bjlc=b.ttlc
            where a.xmid='${data[0].id}' and a.gysid='${req.body.storage.userid}' order by a.bjwlmxid asc,a.blbj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'}`)
   }
   else if (data[0].zbxs == "自由竞价") {
      var bjmxdata = await usequery(`
         select * from (
         select 
         DENSE_RANK() over(partition by a.bjwlmxid,a.create_by order by blbj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'}) as pm,a.*,b.ttlc,b.ttyy from 
         shipSRM_gys_bjmx a 
         left join shipSRM_xm_ttmx b on a.xmid=b.xmid 
         and a.bjwlmxid=b.bjwlmxid and a.gysid=b.gysid and a.bjlc=b.ttlc
         where a.xmid='${data[0].id}'  
      ) as c where c.pm=1 order by c.bjwlmxid asc,c.blbj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'}`)
   }
   res_util(res, { formdata: data[0], wlmxdata, bjmxdata }, true, "查询成功")
})

//查询供应商竞价报价档案列表通过id
router.post('/query_tb_jj', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   if (data[0].jjzt == '竞价中') {
      //获取倒计时 秒数
      let djs = (new Date(data[0].dqljzsj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
      data[0].djs = djs.toFixed(0)
   }
   else if (data[0].jjzt == '待竞价') {
      //获取倒计时 秒数
      if (data[0].dqlkssj) {
         let djs = (new Date(data[0].dqlkssj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
         // console.log(djs, "一开始的倒计时");
         data[0].djs = djs.toFixed(0)
      }
      else {
         data[0].djs = 999999
      }
   }
   else if (data[0].jjzt == '竞价休息') {
      if (new Date(data[0].dqljzsj).getTime() + (data[0].mljg * 60 * 1000) < new Date().getTime()) {
         let djs = (new Date().getTime() + 8 * 60 * 60 * 1000 - new Date(data[0].dqljzsj).getTime() - (data[0].mljg * 60 * 1000)) / 1000
         data[0].djs = djs.toFixed(0)
      }
      else {
         let djs = (new Date(data[0].dqljzsj).getTime() + (data[0].mljg * 60 * 1000) - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
         data[0].djs = djs.toFixed(0)
      }
   }
   else if (data[0].jjzt == '竞价结束') {
      data[0].djs = 0
   }
   else {
      data[0].djs = 0
   }
   var wlmxdata = await usequery(`
      select d.*,SubQuery.SubTableCount from 
         (
            select b.*,c.dqbj,c.pm from shipSRM_xm_wlmx b left join
            (
               select c.bjwlmxid,c.blbj as dqbj,c.pm,c.gysid from (
                 select bjwlmxid,blbj,gysid,xmid,
                 DENSE_RANK() over(partition by bjwlmxid order by blbj asc) as pm from   
                    (
                     select  ROW_NUMBER() OVER (PARTITION BY bjwlmxid,gysid ORDER BY blbj asc) AS rank, *
                     from shipSRM_gys_bjmx where xmid='${req.body.id}'
                     ) as a  where rank=1
                  ) c
                  where c.gysid='${req.body.storage.userid}'
            ) as c on b.id=c.bjwlmxid
         ) as d
				 OUTER APPLY (SELECT COUNT(*) AS SubTableCount FROM shipSRM_xm_ttmx as tt WHERE  tt.xmid = d.mainid and 
						tt.gysid='${req.body.storage.userid}' and tt.bjwlmxid=d.id) AS SubQuery  
         where d.mainid='${data[0].id}' and SubTableCount = 0`)
   var bjmxdata = await usequery(`select a.*,b.ttlc,b.ttyy from shipSRM_gys_bjmx a left join shipSRM_xm_ttmx b on a.xmid=b.xmid 
            and a.bjwlmxid=b.bjwlmxid and a.gysid=b.gysid and a.bjlc=b.ttlc
            where a.xmid='${data[0].id}' and a.gysid='${req.body.storage.userid}' order by a.bjwlmxid asc,a.blbj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'}`)
   var fjmxdata = await usequery(`select a.*,b.gys_fj,b.gys_bz from shipSRM_xm_fjmx a left join shipSRM_gystb_fjmx b on a.id=b.fjmxid 
    and b.gysid='${req.body.storage.userid}'  where a.mainid='${data[0].id}' `)
   res_util(res, { formdata: data[0], wlmxdata, bjmxdata, fjmxdata }, true, "查询成功")
})

//查询供应商竞价报价档案列表通过id
router.post('/query_gystb_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   var wlmxdata = await usequery(`select a.*,b.tbje,b.tbsm from shipSRM_xm_wlmx a left join shipSRM_zb_tbjl b 
      on a.id=b.bjwlid and b.tbgysid='${req.body.storage.userid}' where a.mainid='${data[0].id}' `)
   var fjmxdata = await usequery(`select a.*,b.gys_fj,b.gys_bz from shipSRM_xm_fjmx a left join shipSRM_gystb_fjmx b on a.id=b.fjmxid 
    and b.gysid='${req.body.storage.userid}'  where a.mainid='${data[0].id}' `)
   res_util(res, { formdata: data[0], wlmxdata, fjmxdata }, true, "查询成功")
})

//查询供应商标前询价档案列表通过id
router.post('/query_gysbqxj_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xm where id='${req.body.id}'`)
   if (data[0].jjzt == '竞价中') {
      //获取倒计时 秒数
      let djs = (new Date(data[0].dqljzsj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
      data[0].djs = djs.toFixed(0)
   }
   else if (data[0].jjzt == '待竞价') {
      //获取倒计时 秒数
      if (data[0].dqlkssj) {
         let djs = (new Date(data[0].dqlkssj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
         // console.log(djs, "一开始的倒计时");
         data[0].djs = djs.toFixed(0)
      }
      else {
         data[0].djs = 999999
      }
   }
   else if (data[0].jjzt == '竞价休息') {
      if (new Date(data[0].dqljzsj).getTime() + (data[0].mljg_bj * 60 * 1000) < new Date().getTime()) {
         let djs = (new Date().getTime() + 8 * 60 * 60 * 1000 - new Date(data[0].dqljzsj).getTime() - (data[0].mljg_bj * 60 * 1000)) / 1000
         data[0].djs = djs.toFixed(0)
      }
      else {
         let djs = (new Date(data[0].dqljzsj).getTime() + (data[0].mljg_bj * 60 * 1000) - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
         data[0].djs = djs.toFixed(0)
      }
   }
   else if (data[0].jjzt == '竞价结束') {
      data[0].djs = 0
   }
   else {
      data[0].djs = 0
   }
   var wlmxdata = await usequery(`
      select d.* from shipSRM_xm a ,
         (
            select b.*,c.dqbj,c.pm,isnull(c.SubTableCount,0) as SubTableCount from shipSRM_xm_wlmx b left join
            (
               select c.bjwlmxid,c.blbj as dqbj,c.pm,SubQuery.SubTableCount from (
                 select bjwlmxid,blbj,gysid,xmid,
                 DENSE_RANK() over(partition by bjwlmxid order by blbj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'}) as pm from   
                    (
                     select  ROW_NUMBER() OVER (PARTITION BY bjwlmxid,gysid ORDER BY blbj ${data[0].jjgz == '价高优先' ? 'desc' : 'asc'}) AS rank, *
                     from shipSRM_gys_bjmx where xmid='${req.body.id}'
                     ) as a  where rank=1
                  ) c 
                  OUTER APPLY (SELECT COUNT(*) AS SubTableCount FROM shipSRM_xm_ttmx as tt WHERE  tt.xmid = c.xmid and 
						tt.gysid =c.gysid and tt.bjwlmxid=c.bjwlmxid) AS SubQuery  
                  where c.gysid='${req.body.storage.userid}'
            ) as c on b.id=c.bjwlmxid
         ) as d  
          where a.id=d.mainid and a.id='${data[0].id}'`)
   var bjmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_gys_bjmx b where a.id=b.xmid and a.id='${data[0].id}'
      and b.gysid='${req.body.storage.userid}'`)
   res_util(res, { formdata: data[0], wlmxdata, bjmxdata }, true, "查询成功")
})

// //查询竞价报价档案列表通过id
// router.post('/query_jb_byid', async function (req, res) {
//    var data = req.body.data
//    var rtdata = await usequery(`select * from shipSRM_xm where id='${data.id}'`)
//    if (rtdata[0].jjzt == '竞价中') {
//       //获取倒计时 秒数
//       let djs = (new Date(rtdata[0].dqljzsj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
//       rtdata[0].djs = djs.toFixed(0)
//    }
//    else if (rtdata[0].jjzt == '待竞价') {
//       //获取倒计时 秒数
//       let djs = (new Date(rtdata[0].dqlkssj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
//       // console.log(djs, "一开始的倒计时");
//       rtdata[0].djs = djs.toFixed(0)
//    }
//    else if (rtdata[0].jjzt == '竞价休息') {
//       if (new Date(rtdata[0].dqljzsj).getTime() + (rtdata[0].mljg * 60 * 1000) < new Date().getTime()) {
//          let djs = (new Date().getTime() + 8 * 60 * 60 * 1000 - new Date(rtdata[0].dqljzsj).getTime() - (rtdata[0].mljg * 60 * 1000)) / 1000
//          rtdata[0].djs = djs.toFixed(0)
//       }
//       else {
//          let djs = (new Date(rtdata[0].dqljzsj).getTime() + (rtdata[0].mljg * 60 * 1000) - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
//          rtdata[0].djs = djs.toFixed(0)
//       }
//    }
//    else if (rtdata[0].jjzt == '竞价结束') {
//       rtdata[0].djs = 0
//    }
//    else {
//       rtdata[0].djs = 0
//    }
//    var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data.id}'`)
//    var bjmxdata = await usequery(`select a.*,b.ttlc,b.ttyy from shipSRM_gys_bjmx a left join shipSRM_xm_ttmx b on a.xmid=b.xmid and a.bjwlmxid=b.bjwlmxid and a.gysid=b.gysid and a.bjlc=b.ttlc
//       where a.xmid='${data.id}' order by a.bjwlmxid asc,a.blbj ${data.jjgz == '价高优先' ? 'desc' : 'asc'}`)
//    var gysmxdata = await usequery(`select * from shipSRM_xm_gysmx where mainid='${data.id}' and  COALESCE(sftt,'否') !='是' order by pm asc`)
//    let ifzz = await checkbysql(`select * from  shipSRM_xm_zbmx where mainid='${data.id}'`)
//    let zzmxdata = []
//    if (ifzz) {
//       zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje from (select * from shipSRM_xm_zbmx where mainid='${data.id}') b left join
//          (select xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl,${data.jjgz == '价高优先' ? 'MAX(blbj) as zzbj' : 'MIN(blbj) as zzbj'},max(id) as id from shipSRM_gys_bjmx  where xmid='${data.id}' GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl) as a
//          on b.mainid = a.xmid and b.bjwlmxid = a.bjwlmxid and b.gysmc = a.gysmc`)
//    }
//    else {
//       // zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje from 
//       //    (select xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl,${data.jjgz == '价高优先' ? 'MAX(blbj) as zzbj' : 'MIN(blbj) as zzbj'},max(id) as id from shipSRM_gys_bjmx  where xmid='${data.id}' GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl) as a
//       //    LEFT JOIN shipSRM_xm_zbmx b on a.xmid =b.mainid and a.bjwlmxid =b.bjwlmxid and a.gysmc=b.gysmc`)
//           zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje from 
//          (select bj.xmid,bj.bjwlmxid,bj.wlmc,bj.wlbm,bj.ggxh,bj.gysmc,bj.sl,${data.jjgz == '价高优先' ? 'MAX(bj.blbj) as zzbj' : 'MIN(bj.blbj) as zzbj'},
//          max(bj.id) as id,SubQuery.SubTableCount from shipSRM_gys_bjmx bj
//          OUTER APPLY (SELECT COUNT(*) AS SubTableCount FROM shipSRM_xm_ttmx as tt WHERE  tt.xmid = bj.xmid and 
// 		   tt.gysid =bj.gysid and tt.bjwlmxid=bj.bjwlmxid) AS SubQuery  
//          where xmid='${data.id}' GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl,SubTableCount) as a
//          LEFT JOIN shipSRM_xm_zbmx b on a.xmid =b.mainid and a.bjwlmxid =b.bjwlmxid and a.gysmc=b.gysmc where a.SubTableCount=0`)
//    }
//    res_util(res, { formdata: rtdata[0], wlmxdata, bjmxdata, zzmxdata, ifzz, gysmxdata }, true, "查询成功")
// })

//查询竞价报价档案列表通过id
router.post('/query_jb_byid', async function (req, res) {
   var data = req.body.data
   console.log(data, "data");
   let zbxs = data.zbxs
   console.log(zbxs, "=================zbxs");
   var rtdata = await usequery(`select * from shipSRM_xm where id='${data.id}'`)
   if(rtdata.length==0){  
      res_util(res, { formdata: data[0], wlmxdata, bjmxdata }, true, "该项目不存在")
      return
   }


   if (rtdata[0].jjzt == '竞价中') {
      //获取倒计时 秒数
      let djs = (new Date(rtdata[0].dqljzsj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
      rtdata[0].djs = djs.toFixed(0)
   }
   else if (rtdata[0].jjzt == '待竞价') {
      //获取倒计时 秒数
      let djs = (new Date(rtdata[0].dqlkssj).getTime() - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
      // console.log(djs, "一开始的倒计时");
      rtdata[0].djs = djs.toFixed(0)
   }
   else if (rtdata[0].jjzt == '竞价休息') {
      if (new Date(rtdata[0].dqljzsj).getTime() + (rtdata[0].mljg * 60 * 1000) < new Date().getTime()) {
         let djs = (new Date().getTime() + 8 * 60 * 60 * 1000 - new Date(rtdata[0].dqljzsj).getTime() - (rtdata[0].mljg * 60 * 1000)) / 1000
         rtdata[0].djs = djs.toFixed(0)
      }
      else {
         let djs = (new Date(rtdata[0].dqljzsj).getTime() + (rtdata[0].mljg * 60 * 1000) - new Date().getTime() - 8 * 60 * 60 * 1000) / 1000
         rtdata[0].djs = djs.toFixed(0)
      }
   }
   else if (rtdata[0].jjzt == '竞价结束') {
      rtdata[0].djs = 0
   }
   else {
      rtdata[0].djs = 0
   }
   var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data.id}'`)
   if (zbxs !== '自由竞价') {
      var bjmxdata = await usequery(`select a.*,b.ttlc,b.ttyy from shipSRM_gys_bjmx a left join shipSRM_xm_ttmx b on a.xmid=b.xmid 
      and a.bjwlmxid=b.bjwlmxid and a.gysid=b.gysid
      where a.xmid='${data.id}' order by a.bjwlmxid asc,a.blbj ${data.jjgz == '价高优先' ? 'desc' : 'asc'}`)
   }
   else {
      var bjmxdata = await usequery(`WITH LatestQuotes AS (
    SELECT 
        a.*,
        ROW_NUMBER() OVER (
            PARTITION BY a.gysid,a.bjwlmxid  -- 按供应商分组取最新报价
            ORDER BY a.bjsj DESC  -- 按报价时间倒序排序
        ) AS rn
    FROM shipsrm_gys_bjmx a
    WHERE a.xmid = '${data.id}'  -- 固定查询指定项目
)
SELECT 
    lq.*,               -- 主表所有字段
    b.ttlc,             -- 关联表字段
    b.ttyy              -- 关联表字段
FROM LatestQuotes lq
LEFT JOIN shipsrm_xm_ttmx b 
    ON lq.xmid = b.xmid             -- 项目ID关联
    AND lq.bjwlmxid = b.bjwlmxid    -- 报价物料明细ID关联
    AND lq.gysid = b.gysid          -- 供应商ID关联
WHERE lq.rn = 1  -- 筛选最新报价记录
ORDER BY 
    lq.bjwlmxid ASC,  -- 先按报价物料明细升序
    lq.blbj ${data.jjgz == '价高优先' ? 'desc' : 'asc'}`)
   }
   var gysmxdata = await usequery(`select * from shipSRM_xm_gysmx where mainid='${data.id}' and  COALESCE(sftt,'否') !='是' order by pm asc`)
   let ifzz = await checkbysql(`select * from  shipSRM_xm_zbmx where mainid='${data.id}'`)
   let zzmxdata = []
   if (ifzz) {
      zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje,b.zbsl,c.zbzt from (select * from shipSRM_xm_zbmx where mainid='${data.id}') b left join
         (select xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl,${data.jjgz == '价高优先' ? 'MAX(blbj) as zzbj' : 'MIN(blbj) as zzbj'},max(id) as id from shipSRM_gys_bjmx  where xmid='${data.id}' 
         GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl) as a
         on b.mainid = a.xmid and b.bjwlmxid = a.bjwlmxid and b.gysmc = a.gysmc
         left join shipSRM_xm_gysmx c on b.mainid=c.mainid and b.gysmc=c.gysmc
         `)
   }
   else {
      // zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje from 
      //    (select xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl,${data.jjgz == '价高优先' ? 'MAX(blbj) as zzbj' : 'MIN(blbj) as zzbj'},max(id) as id from shipSRM_gys_bjmx  where xmid='${data.id}' GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl) as a
      //    LEFT JOIN shipSRM_xm_zbmx b on a.xmid =b.mainid and a.bjwlmxid =b.bjwlmxid and a.gysmc=b.gysmc`)
      zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje from 
         (select bj.xmid,bj.bjwlmxid,bj.wlmc,bj.wlbm,bj.ggxh,bj.gysmc,bj.sl,${data.jjgz == '价高优先' ? 'MAX(bj.blbj) as zzbj' : 'MIN(bj.blbj) as zzbj'},
         max(bj.id) as id,SubQuery.SubTableCount from shipSRM_gys_bjmx bj
         OUTER APPLY (SELECT COUNT(*) AS SubTableCount FROM shipSRM_xm_ttmx as tt WHERE  tt.xmid = bj.xmid and 
		   tt.gysid =bj.gysid and tt.bjwlmxid=bj.bjwlmxid) AS SubQuery  
         where xmid='${data.id}' GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl,SubTableCount) as a
         LEFT JOIN shipSRM_xm_zbmx b on a.xmid =b.mainid and a.bjwlmxid =b.bjwlmxid and a.gysmc=b.gysmc where a.SubTableCount=0`)
   }
   res_util(res, { formdata: rtdata[0], wlmxdata, bjmxdata, zzmxdata, ifzz, gysmxdata }, true, "查询成功")
})

//查询竞价报价档案列表通过id
router.post('/query_zbfp_byid', async function (req, res) {
   var data = req.body.data
   var rtdata = await usequery(`select * from shipSRM_xm where id='${data.id}'`)
   var wlmxdata = await usequery(`select b.* from shipSRM_xm a,shipSRM_xm_wlmx b where a.id=b.mainid and a.id='${data.id}'`)
   var bjmxdata = await usequery(`select a.*,b.ttlc,b.ttyy from shipSRM_gys_bjmx a left join shipSRM_xm_ttmx b on a.xmid=b.xmid 
      and a.bjwlmxid=b.bjwlmxid and a.gysid=b.gysid and a.bjlc=b.ttlc
      where a.xmid='${data.id}' order by a.bjwlmxid asc,a.blbj ${data.jjgz == '价高优先' ? 'desc' : 'asc'}`)
   var gysmxdata = await usequery(`select * from shipSRM_xm_gysmx where mainid='${data.id}' and  COALESCE(sftt,'否') !='是' order by pm asc`)
   let ifzz = await checkbysql(`select * from  shipSRM_xm_zbmx where mainid='${data.id}'`)
   let zzmxdata = []
   //如果是已经中标确认过了
   if (ifzz) {
      zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje,b.zbsl,c.zbzt,d.sl,ISNULL(hist.dj, 0) as lsgmdj,'' as hjcz ,'' as dblsdjcz from (select * from shipSRM_xm_zbmx where mainid='${data.id}') b left join
         (select xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,${data.jjgz == '价高优先' ? 'MAX(blbj) as zzbj' : 'MIN(blbj) as zzbj'},max(id) as id from shipSRM_gys_bjmx  where xmid='${data.id}' 
         GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc) as a
         on b.mainid = a.xmid and b.bjwlmxid = a.bjwlmxid and b.gysmc = a.gysmc
         left join shipSRM_xm_gysmx c on b.mainid=c.mainid and b.gysmc=c.gysmc
         left join shipSRM_xm_wlmx d on a.xmid=d.mainid and a.bjwlmxid=d.id
         LEFT JOIN (
            SELECT gysmc, wlbm, dj, ROW_NUMBER() OVER (PARTITION BY gysmc, wlbm ORDER BY create_time DESC) as rn
            FROM shipSRM_xm_zbmx 
            WHERE mainid != '${data.id}'
         ) hist ON b.gysmc = hist.gysmc AND b.wlbm = hist.wlbm AND hist.rn = 2
         `)
   }
   //如果没有进行过中标分配操作
   else {
      // zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje from 
      //    (select xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl,${data.jjgz == '价高优先' ? 'MAX(blbj) as zzbj' : 'MIN(blbj) as zzbj'},max(id) as id from shipSRM_gys_bjmx  where xmid='${data.id}' GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl) as a
      //    LEFT JOIN shipSRM_xm_zbmx b on a.xmid =b.mainid and a.bjwlmxid =b.bjwlmxid and a.gysmc=b.gysmc`)
      zzmxdata = await usequery(`select a.*,b.zbbl,b.zbje,ISNULL(hist.dj, 0) as lsgmdj,'' as hjcz ,'' as dblsdjcz from 
         (select bj.xmid,bj.bjwlmxid,bj.wlmc,bj.wlbm,bj.ggxh,bj.gysmc,bj.sl,${data.jjgz == '价高优先' ? 'MAX(bj.blbj) as zzbj' : 'MIN(bj.blbj) as zzbj'},
         max(bj.id) as id,SubQuery.SubTableCount from shipSRM_gys_bjmx bj
         OUTER APPLY (SELECT COUNT(*) AS SubTableCount FROM shipSRM_xm_ttmx as tt WHERE  tt.xmid = bj.xmid and 
		   tt.gysid =bj.gysid and tt.bjwlmxid=bj.bjwlmxid) AS SubQuery  
         where xmid='${data.id}' GROUP BY xmid,bjwlmxid,wlmc,wlbm,ggxh,gysmc,sl,SubTableCount) as a
         LEFT JOIN shipSRM_xm_zbmx b on a.xmid =b.mainid and a.bjwlmxid =b.bjwlmxid and a.gysmc=b.gysmc 
         left join shipSRM_xm_wlmx c on a.xmid=c.mainid and a.bjwlmxid=c.id 
         LEFT JOIN (
            SELECT gysmc, wlbm, dj, ROW_NUMBER() OVER (PARTITION BY gysmc, wlbm ORDER BY create_time DESC) as rn
            FROM shipSRM_xm_zbmx 
            WHERE mainid != '${data.id}'
         ) hist ON a.gysmc = hist.gysmc AND a.wlbm = hist.wlbm AND hist.rn = 1
         where a.SubTableCount=0`)
   }
   
   // 检查是否有有效的中标分配记录
   let hasValidRecords = zzmxdata && zzmxdata.length > 0
   
   // 如果没有有效记录，设置状态为已确认
   if (!hasValidRecords) {
      rtdata[0].zbfpzt = '已确认'
   }
   
   res_util(res, { formdata: rtdata[0], wlmxdata, bjmxdata, zzmxdata, ifzz, gysmxdata, hasValidRecords }, true, "查询成功")
})

//更新竞价数据
router.post('/update_gysjb_dqlc', async function (req, res) {
   let { id, dqlc, dqlkssj, dqljzsj, jjzt } = req.body
   if (jjzt == '竞价中') {
      await usequery(`update shipSRM_xm set dqlc=${dqlc},dqlkssj='${dqlkssj}',dqljzsj='${dqljzsj}',jjzt='${jjzt}' where id='${id}'`)
   } else {
      await usequery(`update shipSRM_xm set jjzt='${jjzt}' where id='${id}'`)
   }
   res_util(res, {}, true, "更新成功")
})

//新增项目管理档案
router.post('/add_xm', async function (req, res) {
   let { formdata, wlmxdata, zbmxdata, gysmxdata, fjmxdata, pbmddata, storage } = req.body
   let db = new Msdb()
   // var ifwlbm = await checkbysql(` select top 1 * from shipSRM_xm where xmdm='${formdata.xmdm.value}'`)
   // if (ifwlbm) {
   //    res_util(res, { formdata }, false, "新增失败，项目管理编码已存在")
   //    return
   // }
   var xmdmData = await usequery(` select top 1 * from shipSRM_xm order by xmbh desc `)
   var nextxmbh = xmdmData.length > 0 ? parseInt(xmdmData[0].xmbh.slice(-5)) + 1 : '10000'
   formdata.xmbh.value = 'XM' + nextxmbh.toString().padStart(4, '0')
   //不存在编码插入

   var insrtobj = await insert_table(formdata, "shipSRM_xm")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增项目管理', '新增', 'shipSRM_xm')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in wlmxdata) {
      let mxitem = wlmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_xm_wlmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增项目管理物料明细', '新增', 'shipSRM_xm_wlmx')
   }

   for (var mxkey in zbmxdata) {
      let mxitem = zbmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_xm_zbmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增项目管理中标明细', '新增', 'shipSRM_xm_zbmx')
   }

   for (var mxkey in gysmxdata) {
      let mxitem = gysmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      if (mxitem.gysid.value == "") {
         // 生成6位随机密码
         let password = '';
         for (let i = 0; i < 6; i++) {
            password += Math.floor(Math.random() * 10);
         }
         let id = await getxl_util()
         var gysdmData = await usequery(` select top 1 * from shipSRM_gys order by gysdm desc `)
         var nextgysdm = gysdmData.length > 0 ? parseInt(gysdmData[0].gysdm.slice(-5)) + 1 : '10000'
         let gysdm = 'GYS' + nextgysdm.toString().padStart(4, '0')
         let rysql = `insert into  shipSRM_ryda (id,create_time,create_by,xm,js,jsid,dh,zh,mm,ryzt,yhlx,glgys) values 
         ('${id}',getdate(),'${storage.ygxm}','${mxitem.gysmc.value}','供应商','1000604','${mxitem.lxdh.value}','${gysdm}','${password}','正常','租户用户','${mxitem.gysmc.value}')`
         await db.update(rysql)
         await saveczlog_util(rysql, storage, '', '新增临时供应商人员档案', '新增', 'shipSRM_ryda')
         let gyssql = `insert into  shipSRM_gys (id,create_time,create_by,gysmc,xysbdm,fddbr,frlxdh,hzzt,gysdm) values 
         ('${id}',getdate(),'${storage.ygxm}','${mxitem.gysmc.value}','${mxitem.shtyxydm.value}','${mxitem.lxr.value}','${mxitem.lxdh.value}','备选','${gysdm}')`
         await db.update(gyssql)
         await saveczlog_util(gyssql, storage, '', '新增临时供应商档案', '新增', 'shipSRM_gys')
         mxitem.gysid = { dataIndex: "gysid", value: id, ifdb: true }
         var rtmxobj = await insert_table(mxitem, "shipSRM_xm_gysmx")
         await saveczlog_util(rtmxobj.insertsql, storage, '', '新增项目管理供应商明细', '新增', 'shipSRM_xm_gysmx')
      }
      else {
         var rtmxobj = await insert_table(mxitem, "shipSRM_xm_gysmx")
         await saveczlog_util(rtmxobj.insertsql, storage, '', '新增项目管理供应商明细', '新增', 'shipSRM_xm_gysmx')
      }
   }

   for (var mxkey in fjmxdata) {
      let mxitem = fjmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_xm_fjmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增项目管理附件明细', '新增', 'shipSRM_xm_fjmx')
   }

   for (var mxkey in pbmddata) {
      let mxitem = pbmddata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_xm_pbmd")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增项目管理评标名单明细', '新增', 'shipSRM_xm_pbmd')
   }

   res_util(res, { formdata: formdata }, true, "新增成功")
})

//更新项目管理档案
router.post('/update_xm', async function (req, res) {
   let db = new Msdb()
   let { formdata, wlmxdata, zbmxdata, gysmxdata, fjmxdata, pbmddata, wlmxdelidarr, zbmxdelidarr, gysmxdelidarr, fjmxdelidarr, pbmddelidarr, storage } = req.body
   // var ifwlbm = await checkbysql(` select top 1 * from shipSRM_xm where 
   // bm='${formdata.bm.value}' and id !='${formdata.id.value}'`)
   // if (ifwlbm) {
   //    res_util(res, { formdata }, false, "更新失败，物料编码已存在")
   //    return
   // }
   //不存在更新

   let updatobj = await update_table_byid(formdata, "shipSRM_xm")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新项目管理', '更新', 'shipSRM_xm')

   var mainid = updatobj.data.id.value;
   for (var mxkey in wlmxdata) {
      let mxitem = wlmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_xm_wlmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_xm_wlmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_xm_wlmx")
      }
   }
   for (var mxkey in zbmxdata) {
      let mxitem = zbmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_xm_zbmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_xm_zbmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_xm_zbmx")
      }
   }
   for (var mxkey in fjmxdata) {
      let mxitem = fjmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_xm_fjmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_xm_fjmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_xm_fjmx")
      }
   }
   for (var mxkey in pbmddata) {
      let mxitem = pbmddata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_xm_pbmd where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_xm_pbmd")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_xm_pbmd")
      }
   }

   for (var mxkey in gysmxdata) {
      let mxitem = gysmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_xm_gysmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         if (mxitem.gysid.value == "") {
            // 生成6位随机密码
            let password = '';
            for (let i = 0; i < 6; i++) {
               password += Math.floor(Math.random() * 10);
            }
            let id = await getxl_util()
            var gysdmData = await usequery(` select top 1 * from shipSRM_gys order by gysdm desc `)
            var nextgysdm = gysdmData.length > 0 ? parseInt(gysdmData[0].gysdm.slice(-5)) + 1 : '10000'
            let gysdm = 'GYS' + nextgysdm.toString().padStart(4, '0')
            let rysql = `insert into  shipSRM_ryda (id,create_time,create_by,xm,js,jsid,dh,zh,mm,ryzt,yhlx,glgys) values 
            ('${id}',getdate(),'${storage.ygxm}','${mxitem.gysmc.value}','供应商','1000604','${mxitem.lxdh.value}','${gysdm}','${password}','正常','租户用户','${mxitem.gysmc.value}')`
            await db.update(rysql)
            await saveczlog_util(rysql, storage, '', '新增临时供应商人员档案', '新增', 'shipSRM_ryda')
            let gyssql = `insert into  shipSRM_gys (id,create_time,create_by,gysmc,xysbdm,fddbr,frlxdh,hzzt,gysdm) values 
            ('${id}',getdate(),'${storage.ygxm}','${mxitem.gysmc.value}','${mxitem.shtyxydm.value}','${mxitem.lxr.value}','${mxitem.lxdh.value}','备选','${gysdm}')`
            await db.update(gyssql)
            await saveczlog_util(gyssql, storage, '', '新增临时供应商档案', '新增', 'shipSRM_gys')
            mxitem.gysid = { dataIndex: "gysid", value: id, ifdb: true }
            var rtmxobj = await insert_table(mxitem, "shipSRM_xm_gysmx")
            await saveczlog_util(rtmxobj.insertsql, storage, '', '新增项目管理供应商明细', '新增', 'shipSRM_xm_gysmx')
         }
         else {
            rtmxobj = await insert_table(mxitem, "shipSRM_xm_gysmx")
         }
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_xm_gysmx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_xm_wlmx where id in (${wlmxdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_xm_zbmx where id in (${zbmxdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_xm_gysmx where id in (${gysmxdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_xm_fjmx where id in (${fjmxdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_xm_pbmd where id in (${pbmddelidarr.map(item => `'${item}'`).join(',')}) `)
   res_util(res, {}, true, "更新成功")
})

//删除项目管理档案
router.post('/delete_xm', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_xm where id = '${selectedRows.id}'`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除项目管理', '删除', 'shipSRM_xm')
   const delWlmxsql = `delete from shipSRM_xm_wlmx where mainid = '${selectedRows.id}'`
   await db.update(delWlmxsql)
   await saveczlog_util(delWlmxsql, storage, '', '删除项目管理物料明细', '删除', 'shipSRM_xm_wlmx')
   const delZbmxsql = `delete from shipSRM_xm_zbmx where mainid = '${selectedRows.id}'`
   await db.update(delZbmxsql)
   await saveczlog_util(delZbmxsql, storage, '', '删除项目管理中标明细', '删除', 'shipSRM_xm_zbmx')
   const delGysmxsql = `delete from shipSRM_xm_gysmx where mainid = '${selectedRows.id}'`
   await db.update(delGysmxsql)
   await saveczlog_util(delGysmxsql, storage, '', '删除项目管理供应商明细', '删除', 'shipSRM_xm_gysmx')
   const delFjmxsql = `delete from shipSRM_xm_fjmx where mainid = '${selectedRows.id}'`
   await db.update(delFjmxsql)
   await saveczlog_util(delFjmxsql, storage, '', '删除项目管理附件明细', '删除', 'shipSRM_xm_fjmx')
   res_util(res, {}, true, "删除成功")
})


module.exports = router; 